package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.EnrollInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.EnrollRecord;
import com.xmy.cultivate.entity.views.*;
import com.xmy.cultivate.excel.ExcelEnrollRecord;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * 招生数据 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2023-02-10
 */
public interface EnrollInfoMapper extends BaseMapper<EnrollInfo> {


    @Select({"<script>",
            "SELECT * FROM ( SELECT t8.school_id, t1.id, t1.teacher_id,( t1.push_money * t8.grant_num ) AS push_money,",
                      "t1.type,",
                      "t1.is_true,",
                      "t1.is_grant,",
                      "t1.check_type,",
                      "t1.deleted,",
                      "t1.grant_time,",
                      "t1.student_id,",
                      "t1.order_id,",
                      "t1.grant_date,",
                      "t1.add_date,",
                      "t1.operate_teacher_name,",
                      "t1.deal_num,",
                      "t2.year_part,",
                      "t2.quarter_num,",
                      "t3.year_class_id,",
                      "t3.subjects_id,",
                      "SUM( CASE WHEN t1.check_type = 0 THEN 1 ELSE 0 END ) AS not_check_type ",
                      "FROM",
                      "enroll_info_grant_school AS t8",
                      "INNER JOIN enroll_info AS t1 ON t8.order_id = t1.order_id",
                      "LEFT JOIN order_apply AS t2 ON t1.order_id = t2.id",
                      "LEFT JOIN order_detail AS t3 ON t2.id = t3.order_id ",
                      "GROUP BY",
                      "t1.order_id,",
                      "t1.type ",
                      ") AS t ",
                    "${ew.customSqlSegment}",
    "</script>"})
    public abstract IPage<EnrollDetail> getParticulars(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    //提成明细
    @Select({"<script>",
            "SELECT *,getSchoolName(reading_school_id) as school_name,getSubjectsName(subjects_id) as subjects_name,getDictName(year_class_id, 14) as year_class_name FROM ( SELECT t8.school_id, t1.id, t1.teacher_id,( t1.push_money * t8.grant_num ) AS push_money,",
            "t1.type,",
            "t1.is_true,",
            "t1.is_grant,",
            "t1.check_type,",
            "t1.deleted,",
            "t8.grant_no as grant_time,",
            "t1.student_id,",
            "t1.order_id,",
            "t1.grant_date,",
            "t1.true_date_time,",
            "t1.check_date_time,",
            "t1.add_date,",
            "t1.operate_teacher_name,",
            "t1.deal_num,",
            "t2.year_part,",
            "t2.quarter_num,",
            "t3.year_class_id,",
            "t3.subjects_id,",
            "t4.name as student_name,",
            "t2.school_id as reading_school_id,",
            "t8.is_grant AS school_is_grant,",
            "t8.grant_date_time ",
            "FROM",
            "enroll_info_grant_school AS t8",
            "INNER JOIN enroll_info AS t1 ON t8.order_id = t1.order_id",
            "LEFT JOIN order_apply AS t2 ON t1.order_id = t2.id",
            "LEFT JOIN order_detail AS t3 ON t2.id = t3.order_id ",
            "INNER JOIN student as t4 on t1.student_id = t4.id",
            ") AS t ",
            "${ew.customSqlSegment}",
            "</script>"})
    public abstract IPage<EnrollGrantDetail> getParticularsGrantDetail(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);



    @Select("SELECT a.teacher_id,b.name as teacher_name,sum(if(type = 1,deal_num,0))AS inviteNum,sum(if(type = 2,deal_num,0))AS auditionNum,sum(if(type = 3,deal_num,0))AS turnOverNum,sum(push_money) AS push_money from enroll_info as a left join staff as b" +
            " on a.teacher_id = b.id ${ew.customSqlSegment}")
    public abstract IPage<EnrollRecord> getEnrollStudent(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("SELECT a.teacher_id,b.name as teacher_name,sum(if(type = 1,deal_num,0))AS inviteNum,sum(if(type = 2,deal_num,0))AS auditionNum,sum(if(type = 3,deal_num,0))AS turnOverNum,sum(push_money) AS push_money from enroll_info as a left join staff as b" +
            " on a.teacher_id = b.id ${ew.customSqlSegment}")
    public abstract List<ExcelEnrollRecord> getExcelEnrollStudent(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from enroll_info ${ew.customSqlSegment}")
    public abstract List<EnrollInfo> getListByTeacher(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from (select t1.*,t2.year_part,t2.quarter_num from enroll_info as t1 LEFT JOIN order_apply as t2 on t1.order_id = t2.id) as t ${ew.customSqlSegment}")
    public abstract List<EnrollInfo> getEnrollInfoList(@Param(Constants.WRAPPER) Wrapper wrapper);

    //数据汇总列表
    @Select("select *,SUM(IFNULL(push_money,0)) AS commissionTotal,SUM(CASE WHEN is_true = 1 AND check_type = 1 THEN push_money ELSE 0 END) AS distributionTotal,SUM(CASE WHEN is_true = 0 OR (is_true = 1 AND check_type = 0) THEN push_money ELSE 0 END) AS distributionNotTotal,SUM(CASE WHEN is_true = 0 AND is_return = 1 THEN push_money ELSE 0 END) AS freezeTotal,SUM(CASE WHEN is_grant = 1 THEN push_money ELSE 0 END) AS issuedTotal,SUM(CASE WHEN is_true = 1 AND check_type = 1 AND is_grant = 0 THEN push_money ELSE 0 END) AS issuedNotTotal from (select t1.*,t2.year_part,t2.quarter_num,t3.year_class_id from enroll_info as t1 LEFT JOIN order_apply as t2 on t1.order_id = t2.id LEFT JOIN order_detail as t3 on t2.id = t3.order_id) as t ${ew.customSqlSegment}")
    public abstract IPage<EnrollSummary> getParticularsDataSummary(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    //提成发放列表
    @Select("select *,getDictName(year_class_id, 14) as year_class_name,getSubjectsName(subjects_id) as subjects_name,grant_date_time AS operate_time,SUM(IFNULL(push_money,0)) AS commissionTotal,SUM(IF(type = 1,IFNULL(push_money,0),0)) AS invitationAmount,SUM(IF(type = 2,IFNULL(push_money,0),0)) AS trialListeningAmount,SUM(IF(type = 3,IFNULL(push_money,0),0)) AS transactionAmount,SUM(IF( type = 4, IFNULL( push_money, 0 ), 0 )) AS phoneAmount,GROUP_CONCAT(teacher_name) as enroll_teacher_str,GROUP_CONCAT(type) as enroll_type_str  from (select t8.school_id,t1.teacher_id,(t1.push_money*t8.grant_num) as push_money,t1.type,t1.is_true,t1.is_grant,t1.check_type,t1.id,t1.deleted,t8.grant_no as grant_time,t1.student_id,t1.order_id,t1.grant_date,t1.true_date_time,t1.check_date_time,t1.add_date,t1.operate_teacher_name,t2.year_part,t2.quarter_num,t3.year_class_id,t3.subjects_id,t4.`name` AS school_name,t5.`name` AS teacher_name,t6.`name` AS student_name,t8.is_grant as school_is_grant,t8.grant_date_time,(CASE WHEN t1.true_date_time IS NOT NULL AND t1.check_date_time IS NOT NULL AND t1.true_date_time >= t1.check_date_time THEN t1.true_date_time WHEN t1.true_date_time IS NOT NULL  AND t1.check_date_time IS NOT NULL AND t1.true_date_time < t1.check_date_time THEN t1.check_date_time ELSE t1.true_date_time END) AS max_true_date_time  from enroll_info_grant_school as t8 INNER JOIN  enroll_info as t1 on t8.order_id = t1.order_id INNER JOIN order_apply as t2 on t1.order_id = t2.id and t2.status <>2 INNER JOIN order_detail as t3 on t2.id = t3.order_id LEFT JOIN organization AS t4 ON t3.school_id = t4.id LEFT JOIN staff AS t5 ON t1.teacher_id = t5.id LEFT JOIN student AS t6 ON t1.student_id = t6.id) as t ${ew.customSqlSegment}")
    public abstract IPage<EnrollGrantOperate> getParticularsGrant(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select({"<script>",
            "SELECT",
                      "SUM(push_money) AS push_money ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t8.school_id,",
                        "t1.teacher_id,",
                        "( t1.push_money * t8.grant_num ) AS push_money,",
                        "t1.type,",
                        "t1.is_true,",
                        "t1.is_grant,",
                        "t1.check_type,",
                        "t1.deleted,",
                        "t8.grant_no as grant_time,",
                        "t1.student_id,",
                        "t1.order_id,",
                        "t1.grant_date,",
                        "t1.add_date,",
                        "t1.operate_teacher_name,",
                        "t1.true_date_time,",
                        "t1.check_date_time,",
                        "t2.year_part,",
                        "t2.quarter_num,",
                        "t3.year_class_id,",
                        "t3.subjects_id,",
                        "t4.`name` AS school_name,",
                        "t5.`name` AS teacher_name,",
                        "t6.`name` AS student_name,",
                        "t8.is_grant as school_is_grant,",
                        "t8.grant_date_time",
                      "FROM",
                        "enroll_info_grant_school AS t8",
                        "INNER JOIN enroll_info AS t1 ON t8.order_id = t1.order_id",
                        "INNER JOIN order_apply AS t2 ON t1.order_id = t2.id and t2.status &lt;&gt; 2",
                        "INNER JOIN order_detail AS t3 ON t2.id = t3.order_id",
                        "LEFT JOIN organization AS t4 ON t3.school_id = t4.id",
                        "LEFT JOIN staff AS t5 ON t1.teacher_id = t5.id",
                        "LEFT JOIN student AS t6 ON t1.student_id = t6.id ",
                      ") AS t01 ",
                    "WHERE",
                      "school_id = ${schoolId} ",
                      "AND teacher_id = ${teacherId} ",
                       "and ((is_grant = 0 and school_is_grant=0) or (is_grant = 0 and school_is_grant=1) or (is_grant = 1 and school_is_grant=0))",
                      "<when test = '(startDate!=null and startDate != &apos;&apos;) and (endDate != null and endDate != &apos;&apos;)'>",
                      "AND add_date BETWEEN #{startDate} AND #{endDate} ",
                       "</when>",
                        "<when test = '(startTrueDate!=null and startTrueDate != &apos;&apos;) and (endTrueDate != null and endTrueDate != &apos;&apos;)'>",
                            "AND true_date_time BETWEEN #{startTrueDate} AND #{endTrueDate} ",
                            "AND check_date_time BETWEEN #{startTrueDate} AND #{endTrueDate} ",
                        "</when>",
                      "AND is_true = 1 ",
                      "AND check_type = 1 ",
                      "AND deleted = 0 ",
    "</script>"})
    public abstract BigDecimal getParticularsTotal(@Param("schoolId") Long schoolId,
                                                   @Param("teacherId") Long teacherId,
                                                   @Param("startDate") String startDate,
                                                   @Param("endDate") String endDate,
                                                   @Param("startTrueDate") String startTrueDate,
                                                   @Param("endTrueDate") String endTrueDate
                                                   );

    @Update({"<script>",
            "UPDATE enroll_info AS t1",
                    "INNER JOIN enroll_info_grant_school AS t2 ON t1.order_id = t2.order_id ",
                    "AND ((t2.is_grant = 0 and t1.is_grant = 0) or (t2.is_grant = 1 and t1.is_grant = 0) or (t2.is_grant = 0 and t1.is_grant = 1))",
                    "SET t1.is_grant = 1,",
                    "t2.is_grant = 1,",
                    "t2.grant_date_time = now(),",
                    "t2.grant_no = ${grantNo},",
                    "t1.grant_date = #{grantDate},",
                    "t1.grant_time = ${grantTime},",
                    "t1.operate_teacher_id = ${operateTeacherId},",
                    "t1.operate_teacher_name = #{operateTeacherName} ",
                    "WHERE",
                      "t2.school_id = ${schoolId} ",
                      "AND t1.teacher_id = ${teacherId}",
                        "<when test = '(startDate!=null and startDate != &apos;&apos;) and (endDate != null and endDate != &apos;&apos;)'>",
                        "AND t1.add_date BETWEEN #{startDate} AND #{endDate} ",
                        "</when>",
                        "<when test = '(startTrueDate!=null and startTrueDate != &apos;&apos;) and (endTrueDate != null and endTrueDate != &apos;&apos;)'>",
                        "AND t1.true_date_time BETWEEN #{startTrueDate} AND #{endTrueDate} ",
                        "AND t1.check_date_time BETWEEN #{startTrueDate} AND #{endTrueDate} ",
                        "</when>",
                      "AND t1.is_true = 1 ",
                      "AND t1.check_type = 1 ",
                      "AND t1.deleted = 0 ",
    "</script>"})
    public abstract Integer particulartsUpdate(@Param("schoolId") Long schoolId,
                                               @Param("teacherId") Long teacherId,
                                               @Param("startDate") String startDate,
                                               @Param("endDate") String endDate,
                                               @Param("grantDate") String grantDate,
                                               @Param("grantTime") Long grantTime,
                                               @Param("operateTeacherId") Long operateTeacherId,
                                               @Param("operateTeacherName") String operateTeacherName,
                                               @Param("startTrueDate") String startTrueDate,
                                               @Param("endTrueDate") String endTrueDate,
                                               @Param("grantNo") Long grantNo
                                               );


    /**
     * 获取有招生信息的学生
     * @param yearPart
     * @param quarterNum
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "t1.* ",
                    "FROM",
                    "enroll_info AS t1",
                    "INNER JOIN order_apply AS t2 ON t1.order_id = t2.id ",
                    "AND t1.deleted = t2.deleted ",
                    "WHERE",
                    "t2.year_part = ${yearPart} ",
                    "AND t2.quarter_num = ${quarterNum} ",
                    "AND t2.`status` &lt;&gt; 2 ",
                    "AND t2.deleted = 0 ",
                    "GROUP BY",
                    "t2.id",
            "</script>"})
    public abstract List<EnrollInfo> getEnrollStudentList(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum);

    @Select("select MIN(add_date) as min_add_date,MAX(add_date) as max_add_date from enroll_info where team_id = ${teamId} and (add_date < '${startDate}' OR add_date > '${endDate}') and deleted = 0 and is_return =0")
    public abstract EnrollInfo getMinAndMaxDate(@Param("teamId") Long teamId, @Param("startDate") String startDate,@Param("endDate") String endDate);


    @Select("select t1.* from enroll_info as t1 INNER JOIN order_apply as t2 on t1.order_id = t2.id where t1.add_date >= '2024-08-04' and t2.`status`<>2")
    public abstract List<EnrollInfo> getDataList();

    @Select({"<script>",
            "SELECT",
                      "IFNULL( SUM( count ), 0 ) AS isGrant,",
                      "GROUP_CONCAT( teacher_name ) AS teacher_name ",
                    "FROM",
                      "(",
                      "SELECT",
                        "count(*) AS count,",
                        "teacher_id,",
                        "getStaffNameForId ( teacher_id ) AS teacher_name ",
                      "FROM",
                        "enroll_info ",
                      "WHERE",
                        "order_id = ${orderId} ",
                        "AND deleted = 0 ",
                        "AND is_grant = 1 ",
                      "GROUP BY",
                      "teacher_id ",
                      ") AS t",
            "</script>"})
    public abstract EnrollInfo getGrantCount(@Param("orderId") Long orderId);
}
